package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import net.sf.json.JSONArray;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.common.Format;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.dao.ItemDao;
import com.powerunion.datacollection.model.ItemInfo;
import com.powerunion.datacollection.model.jsonObject.ItemInfoJSONObject;
import com.powerunion.datacollection.model.query.ItemInfoQuery;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameterSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;

public class ItemDaoImpl extends JdbcBase implements ItemDao {
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			ItemDaoImpl.class);

	public boolean delete(String[] codes) throws DaoException {
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "delete");
		}
		String sql = "update dc_item_info set IF_DEL = 1 where id = ? ";
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);

		for (int i = 0; i < codes.length; i++) {
			SqlParameterSet spSet = new SqlParameterSet();
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR, codes[i]));
			update.addBatchParameter(spSet);
		}
		try {
			update.access();
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("delete throw of error!");
		}

		int[] effectCount = update.getBatchEffectCounts();
		if (effectCount != null && effectCount.length > 0) {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "元素信息"
						+ Constants.SUCCESS);
			}
			return true;
		} else {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "元素信息"
						+ Constants.FAIL);
			}
			return false;
		}

	}

	public String save(ItemInfo itemInfo) throws DaoException{
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "save");
		}
		String sql = "";
		String resultId = "";
		logger.debug("iteminfo.id:"+itemInfo.getId());
		if (StringUtils.isEmpty(itemInfo.getId())) {// 新增
			String id = String.valueOf(this.getSeq("dc_item_info"));
			itemInfo.setId(id);
			StringBuffer sqlSave = new StringBuffer();
			sqlSave
					.append(
							"insert into dc_item_info (item_name,item_desc,CRT_USER,CRT_DATE,MODI_USER,MODI_DATE,IF_DEL,")
					.append(
							"category_id,item_type,id) values(?,?,?,?,?,?,?,?,?,?)");
			sql = sqlSave.toString();
		} else {// 修改
			StringBuffer sqlUpdate = new StringBuffer();
			sqlUpdate
					.append(
							"update dc_item_info set item_name =?, item_desc=?,CRT_USER=?,")
					.append(
							"CRT_DATE=?, MODI_USER =? ,MODI_DATE =?,IF_DEL=?,category_id =?,item_type=? where id =?");
			sql = sqlUpdate.toString();
		}

		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);
		update.addParameter(new SqlParameter(SqlType.VARCHAR, itemInfo
				.getItemName()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR, itemInfo
				.getItemDesc()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR, itemInfo
				.getCrtUser()));
		update.addParameter(new SqlParameter(SqlType.DATE, new java.sql.Date(
				itemInfo.getCrtDate().getTime())));
		update.addParameter(new SqlParameter(SqlType.VARCHAR, itemInfo
				.getModiUser()));
		update.addParameter(new SqlParameter(SqlType.DATE, new java.sql.Date(
				itemInfo.getModiDate().getTime())));
		update.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
				itemInfo.getIfDel())));
		update.addParameter(new SqlParameter(SqlType.VARCHAR, itemInfo
				.getCategoryId()));
		update.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
				itemInfo.getItemType())));
		update
				.addParameter(new SqlParameter(SqlType.VARCHAR, itemInfo
						.getId()));
		try{
			update.access();
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("保存元素信息出错！");
		}
		
		int effectCount = update.getEffectCounts();
		if (effectCount > 0) {

			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "itemInfo信息"
						+ Constants.SUCCESS);
			}
			resultId = itemInfo.getId();
			return resultId;
		} else {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "itemInfo信息"
						+ Constants.FAIL);
			}
			return "";
		}

	}

	public ItemInfo getBykey(String id) throws DaoException{
		ItemInfo itemInfo = new ItemInfo();
		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select id,item_name,item_desc,CRT_USER,CRT_DATE,MODI_USER,MODI_DATE,")
				.append(
						"IF_DEL,i_use,category_id,item_type from dc_item_info where id = ?");
		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR, id));
		try{
			query.access();
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("getBykey throw error!");
		}
	
		if (query.resultSetAvailable()) {// 检查结果集是否可用
			// 获取并处理记录集
			RsDataSet rs = new RsDataSet(query.getSqlResultSet());
			if (rs.rowCount > 0) {
				itemInfo.setId(rs.getFieldValueAsString("id"));
				itemInfo.setItemName(rs.getFieldValueAsString("item_name"));
				itemInfo.setItemDesc(rs.getFieldValueAsString("item_desc"));
				itemInfo.setCrtUser(rs.getFieldValueAsString("CRT_USER"));
				itemInfo.setCrtDate(rs.getFieldValueAsDate("CRT_DATE"));
				itemInfo.setModiUser(rs.getFieldValueAsString("MODI_USER"));
				itemInfo.setModiDate(rs.getFieldValueAsDate("MODI_DATE"));
				itemInfo.setIfDel(rs.getFieldValueAsInteger("IF_DEL")
						.intValue());
				itemInfo.setIUse(rs.getFieldValueAsString("i_use"));
				itemInfo.setCategoryId(rs.getFieldValueAsString("category_id"));
				itemInfo.setItemType(rs.getFieldValueAsInteger("item_type")
						.intValue());

			}
			rs.clearAll();
		}
		return itemInfo;
	}

	public ItemInfo getBykey(String id, int itemType) {
		ItemInfo itemInfo = new ItemInfo();
		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select id,item_name,item_desc,CRT_USER,CRT_DATE,MODI_USER,MODI_DATE,")
				.append(
						"IF_DEL,i_use,category_id,item_type from dc_item_info where id = ? and item_type =?");
		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR, id));
		query.addParameter(new SqlParameter(SqlType.INTEGER, new Integer(
				itemType)));
		query.access();
		if (query.resultSetAvailable()) {// 检查结果集是否可用
			// 获取并处理记录集
			RsDataSet rs = new RsDataSet(query.getSqlResultSet());
			if (rs.rowCount > 0) {
				itemInfo.setId(rs.getFieldValueAsString("id"));
				itemInfo.setItemName(rs.getFieldValueAsString("item_name"));
				itemInfo.setItemDesc(rs.getFieldValueAsString("item_desc"));
				itemInfo.setCrtUser(rs.getFieldValueAsString("CRT_USER"));
				itemInfo.setCrtDate(rs.getFieldValueAsDate("CRT_DATE"));
				itemInfo.setModiUser(rs.getFieldValueAsString("MODI_USER"));
				itemInfo.setModiDate(rs.getFieldValueAsDate("MODI_DATE"));
				itemInfo.setIfDel(rs.getFieldValueAsInteger("IF_DEL")
						.intValue());
				itemInfo.setIUse(rs.getFieldValueAsString("if_use"));
				itemInfo.setCategoryId(rs.getFieldValueAsString("category_id"));
				itemInfo.setItemType(rs.getFieldValueAsInteger("item_type")
						.intValue());

			}
			rs.clearAll();
		}
		return itemInfo;
	}

	protected void setParams(ItemInfoQuery query, PreparedStatement ps)
			throws DaoException {

		int i = 1;
		try {
			if (!StringUtils.isEmpty(query.getCategoryId())) {
				ps.setString(i++, query.getCategoryId());

			}
			if (query.getItemType() != 0) {
				ps.setInt(i++, query.getItemType());

			}
			if (!StringUtils.isEmpty(query.getItemName())) {

				ps.setString(i++, "%" + query.getItemName() + "%");

			}

			if (!StringUtils.isEmpty(query.getModiUser())) {
				ps.setString(i++, query.getModiUser());

			}

			if (!StringUtils.isEmpty(query.getStartDate())) {
				Date startDate = Format.getInstance().stringToDate(
						query.getStartDate(), "yyyy-MM-dd");
				ps.setDate(i++, new java.sql.Date(startDate.getTime()));
			}

			if (!StringUtils.isEmpty(query.getEndDate())) {
				Date endDate = Format.getInstance().stringToDate(
						query.getEndDate(), "yyyy-MM-dd");
				ps
						.setDate(i++, new java.sql.Date(
								endDate.getTime() + 86400000));
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("sqlSearchItemInfo throw error!");
		}

	}

	protected StringBuffer sqlSearchItemInfo(ItemInfoQuery query)
			throws DaoException {
		StringBuffer sqlStr = new StringBuffer();
		sqlStr
				.append(
						"select id,item_name,item_desc,CRT_USER,CRT_DATE,MODI_USER,MODI_DATE,")
				.append(
						"IF_DEL,i_use,category_id,item_type from dc_item_info ");
		StringBuffer where = new StringBuffer();
		where.append(" IF_DEL = 0 ");
		if (query.getCategoryId() != null && !query.getCategoryId().equals("")) {

			where.append(" and category_id = ? ");
		}
		if (query.getItemType() != 0) {
			where.append(" and item_type = ? ");
			if(query.getItemType() == 2){
				where.append(" or item_type = 20 ");
			}
		}

		if (!StringUtils.isEmpty(query.getItemName())) {
			where.append(" and item_name like ? ");
		}

		if (!StringUtils.isEmpty(query.getModiUser())) {
			where.append(" and MODI_USER = ? ");
		}

		if (!StringUtils.isEmpty(query.getStartDate())) {
			where.append(" and MODI_DATE >= ? ");

		}

		if (!StringUtils.isEmpty(query.getEndDate())) {
			where.append(" and MODI_DATE < ? ");
		}

		if (where.length() > 0) {
			sqlStr.append(" where ").append(where.toString());
		}

		String orderBy = " MODI_DATE desc ";

		sqlStr.append(" order by ").append(orderBy);

		return sqlStr;

	}

	public String findItemInfos(ItemInfoQuery query, int start, int limit)
			throws DaoException {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			String sql = this.sqlSearchItemInfo(query).toString();
			// ResultSet.TYPE_SCROLL_INSENSITIVE:可滚动。但是不受其他用户对数据库更改的影响
			// ResultSet.CONCUR_READ_ONLY,只读
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(start + limit);
			this.setParams(query, ps);
			rs = ps.executeQuery();
			// 将游标移动到要输出的第一条记录
			rs.first();
			rs.relative(start - 1);

			List pageList = new ArrayList();
			while (rs.next()) {
				ItemInfoJSONObject itemInfo = this.getItemInfoJSONObject(rs);
				pageList.add(itemInfo);
			}

			// 转化成JSON数组
			JSONArray jsonarr = JSONArray.fromObject(pageList);
			StringBuffer resultStr = new StringBuffer();
			int total = this.totalRecord(conn, query.getCategoryId(), query
					.getItemType());
			resultStr.append("{total:").append(total).append(",results:")
					.append(jsonarr).append("}");
			logger.debug("findItemInfos:" + Constants.SEARCH + "ItemInfos"
					+ Constants.SUCCESS);
			return resultStr.toString();

		} catch (Exception e) {
			logger.error("getItemInfos:" + Constants.SEARCH + "ItemInfos"
					+ Constants.FAIL, e);
			throw new DaoException("search db record error!");
		} finally {
			DBUtils.close(rs, ps, conn);
		}

	}

	public final ItemInfoJSONObject getItemInfoJSONObject(ResultSet rs)
			throws SQLException {
		ItemInfoJSONObject itemInfo = new ItemInfoJSONObject();
		itemInfo.setId(rs.getString("id"));
		itemInfo.setItemName(rs.getString("item_name"));
		itemInfo.setItemDesc(rs.getString("item_desc"));
		itemInfo.setCrtUser(rs.getString("CRT_USER"));
		Date crtDate = rs.getDate("CRT_DATE");
		String crtDateStr = Format.getInstance().dateToYMD(crtDate);
		itemInfo.setCrtDateStr(crtDateStr);
		itemInfo.setModiUser(rs.getString("MODI_USER"));
		Date modiDate = rs.getDate("MODI_DATE");
		String modiDateStr = Format.getInstance().dateToYMD(modiDate);
		itemInfo.setModiDateStr(modiDateStr);
		itemInfo.setIfDel(rs.getInt("IF_DEL"));
		itemInfo.setIUse(rs.getString("i_use"));
		itemInfo.setCategoryId(rs.getString("category_id"));
		itemInfo.setItemType(rs.getInt("item_type"));

		return itemInfo;
	}

	private int totalRecord(Connection conn, String categoryId, int item_type) {
		int count = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String sql = "select count(id) from dc_item_info where category_id = ? and item_type=? and IF_DEL = 0";
			ps = conn.prepareStatement(sql);
			ps.setString(1, categoryId);
			ps.setInt(2, item_type);
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}

		} catch (Exception e) {
			logger.error("totalRecord:" + Constants.SEARCH
					+ "dc_item_info totalRecord" + Constants.FAIL);
			logger.error("totalRecord() of throws : ", e);
		} finally {
			DBUtils.close(rs, ps);
		}
		return count;
	}

}
